package com.amado.easyui2.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.amado.easyui2.dao.StudentDao;
import com.amado.easyui2.dao.impl.UserDaoMysqlImpl.ShopRowMapper;
import com.amado.easyui2.domain.StudentEntity;

@Repository
public class StudentDaoImpl implements StudentDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	@Override
	public void addStudent(StudentEntity idNumber) {
		String sql = "insert into t_student_spreasyui(name,id_number,age,sex,phone,create_name,candi_car,address,learn_salary,census_register) values (?,?,?,?,?,?,?,?,?,?)";
		getJdbcTemplate().update(sql, idNumber.getName(), idNumber.getIdNumber(), idNumber.getAge(), idNumber.getSex(),
				idNumber.getPhone(), idNumber.getCreateName(), idNumber.getCandiCar(), idNumber.getAddress(),idNumber.getLearnSalary(),idNumber.getCensusRegister());

	}

	@Override
	public int deleteStudent(Long[] id) {
		String sql = "update t_student_spreasyui set delete_logo='Y' where id=?";
		for (Long tempId : id) {
			getJdbcTemplate().update(sql, tempId);
		}
		return 1;
	}

	@Override
	public int updateStudent(StudentEntity id) {
//		The method getPhone() is undefined for the type String  
//	getPhone()方法没有为类型字符串定义    因为定义是的studentEntity
		String sql = "update t_student_spreasyui set name=?,id_number=?,age=?,sex=?,phone=?,candi_car=?,learn_salary=?,census_register=? where id=?";
		return getJdbcTemplate().update(sql, id.getName(), id.getIdNumber(), id.getAge(), id.getSex(), id.getPhone(),id.getCandiCar(),
				id.getLearnSalary(),id.getCensusRegister(), id.getId());
	}

	@Override
	public List<StudentEntity> listAll(String name, String idNumber) {
		StringBuilder sql = new StringBuilder("select * from t_student_spreasyui where delete_logo='N'");
		List<Object> listParam = new ArrayList<>();
		if (name != null && !name.trim().equals("")) {
			sql.append("and name like ?");
			listParam.add("%"+name+"%");
		}
		if (idNumber != null && !idNumber.trim().equals("")) {
			sql.append("and id_number like ?");
			listParam.add("%"+idNumber+"%");
		}
		return getJdbcTemplate().query(sql.toString(), listParam.toArray(),new ShopRowMapper());
		
		/*String sql = "SELECT * FROM sys_user where delete_flag='N'";
		List<Object> listParam = new ArrayList<>();
		if (departNum != null && departNum.trim() != "") {
			sql = sql + " and dept_Num =?";
			listParam.add(departNum);
		}
		if (userName != null && userName.trim() != "") {
			sql = sql + " and user_Num like ?";
			listParam.add("%"+userName+"%");
		}
		sql =sql+"limit ?,?";
		System.out.println("sql"+sql);		
		Integer start = (page.getCurrentPage()-1)*page.getRowSize();
		Integer rowSize = page.getRowSize();
		listParam.add(start);
		listParam.add(rowSize);
		return getJdbcTemplate().query(sql, listParam.toArray(), new ShopRowMapper());*/
		
	}

	@Override
	public StudentEntity getById(Long id) {
		String sql = "select * from t_student_spreasyui where id=?";
		try {
			// 如果没有查询到数据会报异常EmptyResultDataAccessException
			return getJdbcTemplate().queryForObject(sql, new Long[] { id }, new ShopRowMapper());
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	class ShopRowMapper implements RowMapper<StudentEntity> {
		// rs为返回结果集，以每行为单位封装着
		@Override
		public StudentEntity mapRow(ResultSet rs, int rowNum) throws SQLException {
			StudentEntity student = new StudentEntity();
			student.setId(rs.getLong("id"));
			student.setName(rs.getString("name"));
			student.setIdNumber(rs.getString("id_number"));
			student.setAge(rs.getString("age"));
			student.setSex(rs.getString("sex"));
			student.setPhone(rs.getString("phone"));
			student.setAddress(rs.getString("address"));
			student.setCensusRegister(rs.getString("census_register"));
			student.setCandiCar(rs.getString("candi_car"));
			student.setLearnSalary(rs.getString("learn_salary"));
			student.setApplyTime(rs.getTimestamp("apply_time"));
			student.setCreateName(rs.getString("create_name"));
			student.setDeleleLogo(rs.getString("delete_logo"));
			return student;
		}
	}
//	@Override
//	public StudentEntity getBy(String userName, String password) {
//			String sql="select * from user where userName=? and passWord=?"; 
//			try {
//				// 如果没有查询到数据会报异常EmptyResultDataAccessException
//				return getJdbcTemplate().queryForObject(sql, new String[] { userName,password }, new ShopRowMapper());
//			} catch (EmptyResultDataAccessException e) {
//				return null;
//			}
//	}

	@Override
	public StudentEntity getByName(String idNumber) {
		String sql="select * from t_student_spreasyui where id_number=?"; 
		try {
			// 如果没有查询到数据会报异常EmptyResultDataAccessException
			return getJdbcTemplate().queryForObject(sql, new String[] { idNumber }, new ShopRowMapper());
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

}
